package com.honey.sql92script.parser;

import java.io.File;
import java.io.StringReader;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import jsqlparser.parser.CCJSqlParserManager;
import jsqlparser.parser.JSqlParser;
import jsqlparser.statement.create.table.ColumnDefinition;
import jsqlparser.statement.create.table.CreateTable;

import com.honey.core.dbmapping.structure.Column;
import com.honey.core.dbmapping.structure.Schema;
import com.honey.core.dbmapping.structure.StructureType;
import com.honey.core.dbmapping.structure.Procedure.ProcedureResultType;
import com.honey.core.dbmapping.structure.ProcedureParameter.ProcedureColumnType;
import com.honey.core.types.FullyQualifiedJavaType;
import com.honey.core.types.JavaTypeResolver;
import com.honey.core.types.JdbcTypeNameTranslator;
import com.honey.core.types.Vendor;
import com.honey.core.utils.StringUtility;

import com.honey.sql92script.ScriptParser;
import com.honey.sql92script.schema.SqlScriptProcedure;
import com.honey.sql92script.schema.SqlScriptTable;
import com.honey.sql92script.schema.SqlScriptProcedureParameter;

/**
 * 按照sql92标准抽象出解析数据库脚本文件虚类,如果数据库不支持sql92标准本类解析失败.
 * 解析数据库类型有: 解析建表语句 建视图语句 建存储过程语句. 解析模型如下:<br />
 * 	create [operation] table|view|procedure [database schema][.] name( <br />
 *   [table column] | [procedure parameter] <br /> 
 *  ) [operation]
 *  end mark  <br />
 *  <br />
 *  
 * @author Administrator
 *
 */
abstract class AbstractScriptParser implements ScriptParser{
	
	/** create 正则表达式 */
	private static Pattern createPattern = Pattern.compile("(?i)create");
	
	/** 数据库结构名称 */
	private String schemaName= null;
	
	private String databaseSchema = null;
	
	/** 数据库结构类型 */
	private StructureType schemaType ;
	
	/** 数据库结构列 */
	private List<Column> columns;
	
	/** 结构 */
	private Schema schema = null;
	
	/**
	 * 构造函数
	 */
	public AbstractScriptParser(){
		this.columns = new ArrayList<Column>();
	}

	@Override
	public void parserScript(String script ){
		//预处理sql语句
		script = preprocessor(script);
		//定位create字符
		Matcher match = createPattern.matcher(script);
		while(match.find()){
			//m.start() 返回匹配到的子字符串在字符串中的索引位置. 
			//m.end()返回匹配到的子字符串的最后一个字符在字符串中的索引位置. 
			//m.group()返回匹配到的子字符串
			int start = match.start();
			
			//定位endMark
			int end = endMark(start, match.end(), script);
			if(end > 0 ){
				String createScript = script.substring(start, end) ;
				int rightParen = createScript.length();
				if(createScript.charAt(rightParen - 1 ) != ')' ){
					rightParen = parseRightParen(createScript);
					if( rightParen > 0){//解析右括号成功
						createScript =createScript.substring(0,  rightParen + 1);
					}else{//解析右括号失败
						failure();
						continue;
					}
				}
				
				int leftParen = parseLeftParen(createScript);
				if(leftParen > 0){
					boolean isFailure=false;
					if(StructureType.TABLE == schemaType ){
						isFailure = parseCreateTableScript( createScript, leftParen,rightParen);
					} else if(StructureType.VIEW == schemaType ){
						
					} else if(StructureType.PROCEDURE == schemaType ){
						isFailure = parseCreateProcedureScript(createScript, leftParen, rightParen);
					}
					if( isFailure ){
						success( createScript );
					}else{
						failure();
						continue;
					}
				}else{
					failure();
					continue;
				}
			}else{
				failure();
				continue;
			}
		}
	}

	@Override
	public void parserFile(String path , String fileCharacter){
		File file =new File(path);
		if(file.exists() && file.isFile()){
			String script = StringUtility.getStringFromFile(path,fileCharacter);
			if(StringUtility.stringHasValue(script) ){
				parserScript(script);
			}
		}
	}
	
	@Override
	public List<Column> columns() {
		
		return this.columns;
	}

	@Override
	public Schema getSchema() {
		
		return this.schema;
	}

	/**
	 * 解析失败
	 */
	private void failure(){
		this.schemaName = null;
		this.schemaType = null;
		this.schema = null;
		this.columns.clear();
	}
	
	/**
	 * 解析成功
	 * @param script
	 */
	private void success(String script){
		if(StructureType.TABLE ==  schemaType  ){
			SqlScriptTable table = new SqlScriptTable();
			table.setSqlScipt(script);
			table.setSchema(this.databaseSchema);
			table.setName(this.schemaName);
			table.setVendor(getVendor());
			this.schema = table;
		}else if(StructureType.PROCEDURE ==  schemaType ){
			SqlScriptProcedure procedure = new SqlScriptProcedure();
			//procedure.setSchema(this.databaseSchema);
			procedure.setName(schemaName);
			procedure.setSqlScipts(script);
			procedure.setVendor(getVendor());
			procedure.setProcedureResultType(ProcedureResultType.PROCEDURE_RESULT_UNKNOWN);
			this.schema = procedure;
		}else{
			failure();
		}
	}
	
	/**
	 * 解析右括号位置
	 * @param script
	 * @return
	 */
	private final int parseRightParen(String script ){
		return script.lastIndexOf(')');
	}
	
	/**
	 * 解析左括号位置同时解析创建脚本类型和名称
	 * @param script
	 */
	private final int parseLeftParen(String script ){
		int leftParen =  script.indexOf('(') ;
		if(leftParen > 0 ){
			String schema = script.substring(0, leftParen);
			String []schemas = schema.split("\\s");
			schemas = StringUtility.cleanup(schemas, null);
			int size =  schemas.length;
			if(size >= 3 ){
				schemaName = schemas[size -1];
				int dot = schemaName.lastIndexOf('.');
				if(dot > 0){
					this.databaseSchema = schemaName.substring(0,dot);
					schemaName =  schemaName.substring(dot+1);
				}
				
				schema = schemas[size -2];
				if("TABLE".equalsIgnoreCase(schema)){
					schemaType = StructureType.TABLE;
				} else if("VIEW".equalsIgnoreCase(schema)){
					schemaType = StructureType.VIEW;
				} else if("PROCEDURE".equalsIgnoreCase(schema)){
					schemaType = StructureType.PROCEDURE;
				}else{
					//如果什么也没有匹配到表示不支持的数据库结构.终止下一步解析
					leftParen = -1 ;
				}
			}else{
				//不符合创建语句数据模型
				leftParen = -1 ;
			}
		}
		return leftParen;
	}
	
	/**
	 * 解析创建数据库脚本语句
	 * @param leftParen 左括号位置
	 * @param rightParen 右括号位置
	 * @param script 建数据库脚本语句
	 * @return
	 */
	private boolean parseCreateTableScript(String script,int leftParen, int rightParen){
		boolean answer = false;
		JSqlParser parser =new CCJSqlParserManager();
		try {
			CreateTable table = (CreateTable)parser.parse(new StringReader(script));
			this.databaseSchema = table.getTable().getSchemaName();
			this.schemaName = table.getTable().toString();
			List<ColumnDefinition>  columns = table.getColumnDefinitions();
			Column col= null;
			for(ColumnDefinition column : columns ) {
				col = createNewTableColumn(column);
				if(col != null)
					this.columns.add( col );
			}
			setTableConstraint(this.columns, table.getIndexes()) ;
			answer = true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return answer;
	}

	/**
	 * 解析创建存储过程sql语句
	 * @param leftParen 左括号位置
	 * @param rightParen 右括号位置
	 * @param script 创建存储过程sql语句
	 * @return
	 */
	private boolean parseCreateProcedureScript(String script,int leftParen, int rightParen){
		boolean answer = false;	
		try{
			String parameter = script.substring(leftParen+1, rightParen);
			String[] tokens =split(parameter);// parameter.split("\\s");
			//tokens = StringUtility.cleanup(tokens, null);
			String item = null;
			SqlScriptProcedureParameter column = null;
			for(int i=0,size=tokens.length;i<size;i++ ){
				column = new SqlScriptProcedureParameter();
				item = tokens[i];
				String []keys = item.split("\\s");
				keys = StringUtility.cleanup(keys, null);
				//解析参数模型(in out inout )
				ProcedureColumnType procedureColumnType = parameterMode(keys[0]);
				if( procedureColumnType != null ){
					column.setProcedureColumnType(procedureColumnType);
				}
				//解析参数名称
				column.setName(keys[1].trim());
				
				//解析数据类型
				int index = keys[0].length() +  keys[1].length() + 1;
				if(index < item.length()){
					String s = item.substring(index, item.length());
					int left =  s.indexOf('(');
					if(left>0){
						column.setDbTypeName(s.substring(0, left).trim());
						
						int right =  s.indexOf(')');
						if(right > left){
							String davalue = s.substring(left+1, right).trim();
							if(davalue.length()>0 ){
								davalue = davalue.replaceAll("\\s", "");
								davalue = davalue.replaceAll("'", "");
								String vale[] = davalue.split(",");
								if( "enum".equalsIgnoreCase(column.getDbTypeName()) ){
									StringBuilder comment = new StringBuilder();
									int maxLength =0;
									for( String v : vale ){
										if(v.length() > maxLength ) maxLength = v.length();
										comment.append(v+":"+v+":"+v+" ");
									}
									column.setComment(comment.toString());
									column.setLength(maxLength);
								}else{
									column.setLength(Long.parseLong(vale[0]));
									if(vale.length>1 )
										column.setScale(Integer.parseInt(vale[1]));
								}
							}
						}else{
							//column.setLength(10);
							throw new Exception();
						}
					}else{
						if(s.indexOf(')')>0 ) return false;
						if(s.charAt(s.length() -1 ) ==',' ){
							column.setDbTypeName(s.substring(0, s.length() -1 ).trim());
						}else{
							column.setDbTypeName(s.trim());
						}
						column.setLength(10);
					}
				}else{
					column.setDbTypeName("integer");
					column.setLength(10);
					column.setScale(3);
				}
				
				column.setJdbcType(JdbcTypeNameTranslator.getJdbcType( column.getDbTypeName() ));
				//转换成java对象类型
				JavaTypeResolver javaTypeResolver = new JavaTypeResolver(false);
		        FullyQualifiedJavaType fullyQualifiedJavaType = javaTypeResolver.calculateJavaType(column);
				if (fullyQualifiedJavaType != null) {
					column.setFullyQualifiedJavaType(fullyQualifiedJavaType);
					columns.add(column);
		        }
				answer = true;
			}
		}catch (Exception e) {
			answer = false;
			System.out.println("存储过程参数语法错误,存储过程参数列表是:" +script);
			e.printStackTrace();
		}
		return answer;
	}

	private String [] split(String str ){
		char []chars = str.toCharArray();
		ArrayList<String> array = new ArrayList<String>();
		StringBuilder sb = new StringBuilder();
		boolean leftParen=false;
		int last =0;
		for(int i=0;i<chars.length;i++  ){
			char c = chars[i];
			sb.append(c);
			switch( c ){
				case 40 : //左括号开始
					leftParen=true; break;
				case 41 : //左括号开始
					if( leftParen ){
						leftParen=false;
					}
					 break;
				case 44 : //逗号
					if( !leftParen ){
						array.add(sb.toString().trim());
						sb.setLength(0);
						last = i;
					}
					break;
			}
		}
		if(last+1< chars.length){
			array.add( str.substring(last+1,chars.length).trim() );
		}
		return array.toArray(new String[array.size()]);
		
		
	}
	
	private final ProcedureColumnType parameterMode(String s ){
		//IN  OUT  INOUT
		ProcedureColumnType answer = null;
		if(StringUtility.stringHasValue(s) ){
			char []chars = s.toCharArray();
			if( chars.length >=2){
				if((chars.length >= 5) && 
						(105 == chars[0] || 73 == chars[0])&&(110 == chars[1] || 78 == chars[1]) &&
						(111 == chars[2] || 79 == chars[2])&&(117 == chars[3] || 85 == chars[3])&&(116 == chars[4] || 84 == chars[4]) ){
					answer = ProcedureColumnType.PROCEDURE_COLUMN_INOUT;
				}else if(  (105 == chars[0] || 73 == chars[0])&&(110 == chars[1] || 78 == chars[1]) ){
					answer = ProcedureColumnType.PROCEDURE_COLUMN_IN;
				}else if((chars.length >= 3) && (111 == chars[0] || 79 == chars[0])&&(117 == chars[1] || 85 == chars[1])&&(116 == chars[2] || 84 == chars[2]) ){
					answer = ProcedureColumnType.PROCEDURE_COLUMN_OUT;
				}
			}
		}
		return answer;
	}
	
	/**
	 * 在进行sql解析之前预处理sql语句 
	 * @param script sql语句
	 * @return 处理好的sql语句
	 */
	abstract String preprocessor(String script);
	
	/**
	 * 结束标记位置
	 * @param matchStart 匹配的开始位置
	 * @param matchEnd  匹配的终止位置
	 * @param script sql 语句
	 * @return 
	 */
	abstract int endMark(int matchStart,int matchEnd , String script) ;
	
	/**
	 * 解析数据库表的列
	 * @param columnDefinition
	 * @return
	 */
	abstract Column createNewTableColumn(ColumnDefinition columnDefinition);
	
	/**
	 * 数据数据库约束
	 * @param columns
	 * @param constraint
	 */
	abstract void setTableConstraint(List<Column> columns,List constraint );
	
	/**
	 * 数据库脚本类型
	 * @return
	 */
	abstract Vendor getVendor();
}
